package com.rdgzs.framework.utilsTools;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.CellView;
import jxl.DateFormulaCell;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelJxlUtils<T> {
	private String EXCEL_SUFFIX=".xls";//excel 文件名
	private List<TitleBO> titles=new ArrayList();//标题    key：变量名   value:标题内容  titles为空时将输出所有内容标题为变量名
	private int startColumnNum=0;//开始输出的行列号
	private int startRowNum=0;//开始输出 的行号
	private WritableFont titleFont=null;//标题行的字体
	private WritableFont contentFont=null;//内容行的字体
	private WritableCellFormat titleFormat=null;//标题栏单元格格式化
	private WritableCellFormat contentFormat=null;//内容栏单元格格式化
	private OutputStream outStream=null;//将生成的excel输出到指定的流中
	private WritableWorkbook workbook=null;//
	private DateFormat dateFormat=new DateFormat("yyyy-MM-dd");
	
	public static void main(String[] args) {
		
	}
	
	public static ExcelJxlUtils getInstance(OutputStream stream) throws WriteException, IOException {
		return new ExcelJxlUtils(stream);
	}
	
	public ExcelJxlUtils(OutputStream out) throws WriteException, IOException{
		this.outStream=out;
		this.workbook=Workbook.createWorkbook(out);
		this.titleFont=this.getBlackTitleFont();	
		this.contentFont=this.getBlackFont();
		this.titleFormat=this.getCellFormat();
		this.contentFormat=this.getCellFormat();
		this.titleFormat.setFont(titleFont);
		this.contentFormat.setFont(contentFont);
	}
	//1.文件名_日期时间
	//2.默认表头字体
	//3.表格样式
	//4.创建初始化工作薄
	//5.将传入的对像List转为Excel内容
	
	private WritableSheet addSheet(String sheetName) throws IOException{
		return workbook.createSheet(sheetName, workbook.getSheets().length);
	}
	
	private WritableSheet addSheet(List<T> objList,String sheetName) throws IOException, RowsExceededException, WriteException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
		this.objs2row(objList,addSheet(sheetName));
		return this.objs2row(objList,addSheet(sheetName));
	}
	
	/**
	 * 将一个对像转为列值
	 * @return
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 * @throws SecurityException 
	 * @throws NoSuchMethodException 
	 * @throws InvocationTargetException 
	 * @throws IllegalArgumentException 
	 * @throws IllegalAccessException 
	 */
	public WritableSheet objs2row(List<T> objList,WritableSheet sheet) throws RowsExceededException, WriteException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
		//1.标头内容、样式、列宽初始化 
		//2.内容初始化 list<Map>
		int x=startColumnNum;
		int y=startRowNum;
		titleInit(sheet,titleFormat);
		for (T obj : objList) {
			Class classz=obj.getClass();
			y++;
			for(TitleBO bo:titles){
				Method method=classz.getMethod(bo.getterName(), null);
				Object returnObj=method.invoke(obj, null);
				Class returnType=method.getReturnType();
				if(returnType == String.class){
					Label cell = new Label(x, y, (String) returnObj);
					sheet.addCell(cell);
				}else if(returnType == Date.class){
					DateTime cell = new DateTime(x, y,  (Date) returnObj);
					sheet.addCell(cell);
				}else if(returnType == Integer.class ){
					jxl.write.Number cell = new jxl.write.Number(x, y,  (Integer)returnObj);
					sheet.addCell(cell);
				}else if(returnType == Long.class ){
					jxl.write.Number cell = new jxl.write.Number(x, y,  (Long)returnObj);
					sheet.addCell(cell);
				}else if(returnType == Double.class){
					jxl.write.Number cell = new jxl.write.Number(x, y,  (Double)returnObj);
					sheet.addCell(cell);
				}
				x++;
			}
		}
		
		return sheet;
	}
	
	/**
	 * 将一个对像转为列值
	 * @return
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 */
	public void titleInit(WritableSheet sheet,WritableCellFormat titleFormat) throws RowsExceededException, WriteException{
		int i=startColumnNum;
		Label cell = null;
		for(TitleBO bo:titles){
			if(bo.getWidth()!=null){//列是否自动调整
				sheet.setColumnView(i, bo.getWidth());
			}else if(bo.isAutoSizeCol()){
				sheet.setColumnView(i, this.colAutoSize());
			}
			if(titleFormat==null){//标题内容 格式化
				cell = new Label(i++, startRowNum, bo.getTitle());
			}else{
				cell = new Label(i++, startRowNum, bo.getTitle(),titleFormat);
			}
			sheet.addCell(cell);
		}
	}
	
	/**
	 * 增加要显示的字段和显示名称
	 * @param title
	 * @param varName
	 * @return
	 */
	public TitleBO addTitle(String title,String varName){
		TitleBO bo=new TitleBO();
		bo.setTitle(title);
		bo.setVarName(varName);
		titles.add(bo);
		return bo;
	}
	
	/**
	 * 红色加粗标题
	 * @return
	 */
	public WritableFont getRedTitleFont(){
		return new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.RED);
	}
	
	/**
	 * 黑色加粗标题
	 * @return
	 */
	public WritableFont getBlackTitleFont(){
		return new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.RED);
	}
	
	/**
	 * 黑色字体
	 * @return
	 */
	public WritableFont getBlackFont(){
		return new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
	}
	
	/**
	 * 单元格式化器
	 * @return
	 * @throws WriteException
	 */
	public WritableCellFormat getCellFormat() throws WriteException{
		WritableCellFormat format = new WritableCellFormat(); // 表格样式
		//format.setFont(getRedFont());
		format.setAlignment(Alignment.CENTRE);// 水平居中
		format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直居中
		format.setBorder(Border.ALL, BorderLineStyle.THIN);// 线条
		return format;
	}
	
	/**
	 * 设置列的自动调整
	 * @return
	 */
	public CellView colAutoSize(){
		CellView cv = new CellView();  
		cv.setAutosize(true);
		return cv;
	}
	
	/**
	 * excel输出的初始化信息
	 * @param response
	 * @param fileName
	 * @return
	 */
	public HttpServletResponse responseInit(HttpServletResponse response,final String fileName){
		String timeStr=DateTimeUtils.SDF_YYYYMMDDHHMMSS.format(new Date());
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName+"_"+timeStr);
		return response;
	}

	public String getEXCEL_SUFFIX() {
		return EXCEL_SUFFIX;
	}

	public void setEXCEL_SUFFIX(String eXCEL_SUFFIX) {
		EXCEL_SUFFIX = eXCEL_SUFFIX;
	}

	public List<TitleBO> getTitles() {
		return titles;
	}

	public void setTitles(List<TitleBO> titles) {
		this.titles = titles;
	}

	public int getStartColumnNum() {
		return startColumnNum;
	}

	public void setStartColumnNum(int startColumnNum) {
		this.startColumnNum = startColumnNum;
	}

	public int getStartRowNum() {
		return startRowNum;
	}

	public void setStartRowNum(int startRowNum) {
		this.startRowNum = startRowNum;
	}

	public WritableFont getTitleFont() {
		return titleFont;
	}

	public void setTitleFont(WritableFont titleFont) {
		this.titleFont = titleFont;
	}

	public WritableFont getContentFont() {
		return contentFont;
	}

	public void setContentFont(WritableFont contentFont) {
		this.contentFont = contentFont;
	}

	public WritableCellFormat getTitleFormat() {
		return titleFormat;
	}

	public void setTitleFormat(WritableCellFormat titleFormat) {
		this.titleFormat = titleFormat;
	}

	public WritableCellFormat getContentFormat() {
		return contentFormat;
	}

	public void setContentFormat(WritableCellFormat contentFormat) {
		this.contentFormat = contentFormat;
	}

	public OutputStream getOut() {
		return outStream;
	}

	public void setOut(OutputStream out) {
		this.outStream = out;
	}

	public WritableWorkbook getWorkbook() {
		return workbook;
	}

	public void setWorkbook(WritableWorkbook workbook) {
		this.workbook = workbook;
	}
	
	
	
}



/**
 * 存放用户的表头信息
 * @author NEW
 *  说明：width、isAutoSizeCol 同时存在时会以先以width优先
 */
class TitleBO{
	private String title;//表题要显示的名称
	private String varName;//对应类里的变量名：会根据此变量值计算getter
	private Integer width;//显示的宽度 
	private boolean isAutoSizeCol=true;//列是否自己调整大小 
	
	
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getVarName() {
		return varName;
	}
	public String getterName() {
		return "get"+varName.substring(0, 1).toUpperCase()+varName.substring(1);
	}
	public void setVarName(String varName) {
		this.varName = varName;
	}
	public boolean isAutoSizeCol() {
		return isAutoSizeCol;
	}
	public void setAutoSizeCol(boolean isAutoSizeCol) {
		this.isAutoSizeCol = isAutoSizeCol;
	}
	public Integer getWidth() {
		return width;
	}
	public void setWidth(Integer width) {
		this.width = width;
	}
}




